package com.trade.module.database.operation;

import com.trade.common.utils.XaUtil;
import com.trade.module.database.basic.ConstConfig;
import com.trade.module.database.basic.CustomDataSource;
import com.trade.module.database.basic.DataSourceManager;
import com.trade.module.database.meta.*;
import com.trade.module.database.operation.DatabaseAnalyzer;
import lombok.extern.log4j.Log4j;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.MapListHandler;
import org.apache.commons.dbutils.handlers.ScalarHandler;
import org.apache.commons.lang.StringUtils;

import java.io.Serializable;
import java.sql.*;
import java.util.*;

/**
 * @Desc: 数据库查询工具类
 * @Version: v1.0 2017/12/6 10:39
 * @Author: zfb
 * @JDK Version: 1.8.0_51
 */
@Log4j
public class DBQueryUtils implements Serializable {

    private static final long serialVersionUID = -3732347780814867377L;
    private CustomDataSource dataSource;

    /**
     * 数据库驱动关键字
     */
    public interface DBDriverKW {
        String MYSQL = "mysql";
        String ORACLE = "oracle";
        String SQL_SERVER = "sqlserver";
        String DM = "dm.jdbc";
    }

    public DBQueryUtils(String driverName, String dbUrl, String userName, String password, int connPoolSize) {
        try {
            this.dataSource = DataSourceManager.getInstance().getCustomDataSource(driverName, dbUrl, userName, password, connPoolSize);
        } catch (SQLException e) {
            log.error("获取数据源失败："+e);
        }
    }

    /**
     * 根据数据库驱动获取数据库类型
     * @param driverClassName 驱动类名
     * @return ConstConfig.DBType
     */
    private ConstConfig.DBType getDbType(String driverClassName) {
        ConstConfig.DBType dbType = null;
        if (driverClassName.toLowerCase().contains(DBDriverKW.MYSQL)) {
            dbType = ConstConfig.DBType.MYSQL;
        } else if (driverClassName.toLowerCase().contains(DBDriverKW.ORACLE)) {
            dbType = ConstConfig.DBType.ORACLE;
        } else if (driverClassName.toLowerCase().contains(DBDriverKW.SQL_SERVER)) {
            dbType = ConstConfig.DBType.SQLSERVER;
        } else if (driverClassName.toLowerCase().contains(DBDriverKW.DM)) {
            dbType = ConstConfig.DBType.DAMENG;
        }
        return dbType;
    }

    /**
     * 获取表中所有数据
     * @param tableName 表名称
     * @return List 数据集合
     * @throws SQLException sql异常
     */
    public List queryTableData(String tableName) throws SQLException {
        QueryRunner runner = new QueryRunner(dataSource.getDds());
        String sql = "select * from " + tableName;
        return runner.query(sql, new MapListHandler());
    }

    /**
     * 分页查询
     * @param tableName   表名称
     * @param currentPage 当前页
     * @param numPerPage  每页条数
     * @param sql         查询语句
     * @return PageInfo
     * @throws Exception
     */
    public PageInfo queryPageinfo(String tableName, long currentPage, int numPerPage, String sql) throws Exception {
        String driverClassName = dataSource.getDds().getDriverClassName();
        ConstConfig.DBType dbType = getDbType(driverClassName);
        QueryRunner queryRunner = new QueryRunner(dataSource.getDds());
        return new Pagination(dbType, sql, currentPage, numPerPage, new MapListHandler(), queryRunner, tableName).queryPageinfo();
    }

    /**
     * 查询字段最大值
     * @param sql
     * @return
     * @throws SQLException
     */
    public Object queryFieldMaxValue(String sql) throws SQLException {
        QueryRunner queryRunner = new QueryRunner(dataSource.getDds());
        return queryRunner.query(sql, new ScalarHandler());
    }

    /**
     * 按分页的方式查询表的数据
     *
     * @param dbName      库名称
     * @param tableName   表名称
     * @param currentPage 当前页
     * @param numPerPage  每页条数
     * @return 返回页面信息，以及数据结果
     * @throws SQLException
     */
    public PageInfo queryTableDataByPaging(String dbName, String tableName, long currentPage, int numPerPage, String sql) throws Exception {
        return queryTableDataByPaging(dbName,tableName,currentPage,numPerPage,sql,null);
    }

    /**
     * 按分页的方式查询表的数据
     *
     * @param dbName      库名称
     * @param tableName   表名称
     * @param currentPage 当前页
     * @param numPerPage  每页条数
     * @param totalRows   总记录数
     * @return 返回页面信息，以及数据结果
     * @throws SQLException
     */
    public PageInfo queryTableDataByPaging(String dbName, String tableName, long currentPage, int numPerPage, String sql, Long totalRows) throws Exception {
        String driverClassName = dataSource.getDds().getDriverClassName();
        ConstConfig.DBType dbType = getDbType(driverClassName);

        //判断如果是SQLServer数据库则需要查出该表的中的 主键，或者有唯一索引的字段，以便分页中使用
        String pkColumnName = null;
        if (dbType == ConstConfig.DBType.SQLSERVER) {
            List<PrimaryKeyMetaData> primaryKeyList = getPrimaryKeyList(dbName, tableName);
            if (primaryKeyList != null && primaryKeyList.size() > 0) {
                PrimaryKeyMetaData primaryKeyMetaData = primaryKeyList.get(0);
                pkColumnName = primaryKeyMetaData.getName();
            } else {
                List<ColumnMetaData> columns = getColumns(dbName, tableName);
                if (columns != null && columns.size() > 0) {
                    pkColumnName = columns.get(0).getName();
                }
            }
        }
        QueryRunner queryRunner = new QueryRunner(dataSource.getDds());
        if(totalRows == null){
            return new Pagination(dbType, sql, currentPage, numPerPage, new MapListHandler(), queryRunner, tableName).queryData(pkColumnName);
        }
        return new Pagination(dbType, sql, currentPage, numPerPage, new MapListHandler(), queryRunner, tableName, totalRows).queryData(pkColumnName);
    }

    /**
     * 批量导入数据
     * @param tableName     表名称
     * @param dbName        库名称
     * @param dataList      数据集合
     * @return boolean      导入状态（ture/false）
     * @throws SQLException sql异常
     */
    public boolean insertBatchData(String tableName, String dbName, List<Map<String, String>> dataList) throws SQLException {
        boolean result = true;
        if(StringUtils.isNotBlank(tableName) && XaUtil.isNotEmpty(dataList)) {
            try {
                //获取字段名数组，过滤字段名称为空的情况
                Map<String, String> columnMap = dataList.get(0);
                List<String> columnList = new ArrayList<>();
                for (String columnName : columnMap.keySet()) {
                    if (StringUtils.isNotBlank(columnName)) {
                        columnList.add(columnName);
                    }
                }
                String[] columnArray = columnList.toArray(new String[0]);

                //获取数据库类型
                String driverName = dataSource.getDds().getDriverClassName();
                ConstConfig.DBType dbType = getDbType(driverName);

                List<Object[]> paramsList = new ArrayList<>();
                if (dbType == ConstConfig.DBType.ORACLE || dbType == ConstConfig.DBType.DAMENG) {
                    //根据库名、表名获取表中所有列
                    List<ColumnMetaData> columns = getColumns(dbName, tableName);
                    //遍历批量参数

                    for (Map<String,String> dataMap : dataList) {
                        //为了解决set是无序的集合，避免数据错乱，需要循环取值
                        List<Object> data = new ArrayList<>();
                        for(String columnName : columnArray){
                            if (dataMap.containsKey(columnName)) {
                                //列值
                                String columnValue = dataMap.get(columnName);
                                Object valObj = null;
                                //列类型
                                String cloumnType = getColumnType(columns, columnName);
                                if(StringUtils.isNotBlank(columnValue) && !columnValue.equalsIgnoreCase("null")){
                                    //处理时间类型
                                    if(StringUtils.equalsIgnoreCase(cloumnType, "DATE")){
                                        String value = columnValue.trim();
                                        int indexOf = value.indexOf(" ");
                                        if (indexOf > -1) {
                                            value = value.substring(0, indexOf);
                                        }
                                        valObj = java.sql.Date.valueOf(value);
                                    } else if (StringUtils.startsWithIgnoreCase(cloumnType, "DATETIME")) {
                                        valObj = java.sql.Timestamp.valueOf(columnValue);
                                    } else if (StringUtils.startsWithIgnoreCase(cloumnType, "TIMESTAMP")) {
                                        valObj = java.sql.Timestamp.valueOf(columnValue);
                                    }
                                }
                                data.add(valObj);
                            }
                        }
                        paramsList.add(data.toArray());
                    }

                } else {
                    for (Map<String,String> dataMap : dataList) {
                        //为了解决set是无序的集合，避免数据错乱，需要循环取值
                        List<Object> data = new ArrayList<>();
                        for (String columnName : columnArray) {
                            if (dataMap.containsKey(columnName)) {
                                String columnValue = dataMap.get(columnName);
                                data.add(StringUtils.isBlank(columnValue)?null:columnValue);
                            }
                        }
                        paramsList.add(data.toArray());
                    }
                }

                //组装问号占位符
                List<String> flagList = new ArrayList<>();
                for (String ignored : columnArray) {
                    flagList.add("?");
                }
                //拼接insert语句
                StringBuffer sb = new StringBuffer();
                sb.append("INSERT INTO ");
                switch (dbType) {
                    case DAMENG:
                        sb.append("\"" + dbName.toUpperCase() + "\".\"" + tableName + "\"");
                        sb.append(" (\"");
                        sb.append(StringUtils.join(columnArray, "\",\""));
                        sb.append("\") ");
                        break;
                    case ORACLE:
                        sb.append(tableName);
                        sb.append(" (\"");
                        sb.append(StringUtils.join(columnArray, "\",\""));
                        sb.append("\") ");
                        break;
                    default:
                        sb.append(tableName);
                        sb.append(" (");
                        sb.append(StringUtils.join(columnArray, ","));
                        sb.append(") ");
                        break;
                }
                sb.append(" VALUES ");
                sb.append(" (");
                sb.append(StringUtils.join(flagList, ","));
                sb.append(") ");
                //批量导入执行
                long startTimeSave = System.currentTimeMillis();   //获取开始时间
                QueryRunner queryRunner = new QueryRunner(dataSource.getDds());
                queryRunner.batch(sb.toString(), paramsList.toArray(new Object[0][0]));
                long endTimeSave = System.currentTimeMillis();    //获取结束时间
                //打印日志
                log.info("存储目标数据运行时长：" + (endTimeSave - startTimeSave) + "ms");
            } catch (Exception e) {
                result = false;
                log.error("批量导入数据异常："+e);
            }
        } else {
            result = false;
        }
        return result;
    }



    /**
     * 获取某一个表所有的字段详情信息
     *
     * @param dbName    数据库名
     * @param tableName 表名
     * @return List<ColumnMetaData>
     * @throws SQLException sql异常
     */
    public List<ColumnMetaData> getColumns(String dbName, String tableName) throws SQLException {
        return getColumns(dbName,tableName,null);
    }

    public List<ColumnMetaData> getColumns(String dbName, String tableName, String otherUser) throws SQLException {
        Connection connection = null;
        List<ColumnMetaData> columnMetaData = null;
        try {
            connection = dataSource.getDds().getConnection();
            String driverClassName = dataSource.getDds().getDriverClassName();
            ConstConfig.DBType dbType = getDbType(driverClassName);
            DatabaseMetaData metaData = connection.getMetaData();
            if(StringUtils.isNotBlank(otherUser)){
                columnMetaData = DatabaseAnalyzer.getColumns(metaData, dbName, tableName, dbType, otherUser);
            } else {
                columnMetaData = DatabaseAnalyzer.getColumns(metaData, dbName, tableName, dbType);
            }

        } catch (Exception e) {
            log.error("获取某一个表所有的字段详情信息异常："+e);
        } finally {
            if(connection != null){
                connection.close();
            }
        }
        return columnMetaData;
    }

    /**
     * 查找列名信息集合中的指定列名的字段类型
     *
     * @param columns     列信息集合
     * @param columnName  列名称
     * @return String
     */
    private String getColumnType(List<ColumnMetaData> columns, String columnName) {
        String cloumnType = null;
        for (ColumnMetaData columnMetaData : columns) {
            if (StringUtils.equalsIgnoreCase(columnMetaData.getName(), columnName)) {
                cloumnType = columnMetaData.getType();
            }
        }
        return cloumnType;
    }

    /**
     * 判断表是否存在
     * @param tableName 表名称
     * @return boolean
     */
    public boolean isExistTable(String tableName, ConstConfig.DBType dbType) throws SQLException {
        String sql;
        switch (dbType) {
            case MYSQL:
                sql = "select count(1) from information_schema.tables where  table_name = '" + tableName + "'";
                break;
            case DAMENG:
                sql = "select count(1) from user_tables where TABLE_NAME = '" + tableName + "'";
                break;
            case ORACLE:
                sql = "select count(1) from user_tables where TABLE_NAME = '" + tableName + "'";
                break;
            case SQLSERVER:
                sql = "select count(1) from sys.tables where name='" + tableName + "'";
                break;
            default:
                return false;

        }
        Connection connection = null;
        PreparedStatement ps = null;
        try {
            connection = dataSource.getDds().getConnection();
            ps = connection.prepareStatement(sql);
            ResultSet resultSet = ps.executeQuery();
            while (resultSet.next()) {  //通过next来索引：判断是否有下一个记录
                long count = resultSet.getLong(1);
                return count > 0;
            }
        } finally {
            if (ps != null) {
                ps.close();
            }
            if (connection != null) {
                connection.close();
            }
        }
        return false;
    }

    /**
     * 执行ddl语句，不能回滚
     * @param ddlSqls 语句数组
     * @return boolean
     * @throws SQLException sql异常
     */
    public boolean execDDLSql(String... ddlSqls) throws SQLException {
        Connection connection = null;
        PreparedStatement ps = null;
        try {
            connection = dataSource.getDds().getConnection();
            for (String ddlSql : ddlSqls) {
                ps = connection.prepareStatement(ddlSql);
                ps.executeUpdate();
            }
        } finally {
            if (ps != null) {
                ps.close();
            }
            if (connection != null) {
                connection.close();
            }
        }
        return true;
    }

    /**
     * 删除表数据，根据条件
     *
     * @param tableName 表名
     * @param condMap   条件集合
     * @return boolean
     */
    public boolean delData(String tableName, Map<String, String> condMap) throws SQLException {
        StringBuilder sb = new StringBuilder();
        sb.append("DELETE FROM ");
        sb.append(tableName);
        boolean result;
        if (!condMap.isEmpty()) {
            sb.append(" WHERE 1=1 ");
            Set<Map.Entry<String, String>> entries = condMap.entrySet();
            Iterator<Map.Entry<String, String>> iterator = entries.iterator();
            while (iterator.hasNext()) {
                Map.Entry<String, String> entry = iterator.next();
                sb.append(" AND ");
                sb.append(entry.getKey());
                sb.append("=");
                sb.append("'");
                sb.append(entry.getValue());
                sb.append("'");
            }
        }
        try {
            QueryRunner queryRunner = new QueryRunner(dataSource.getDds());
            queryRunner.update(sb.toString());
            result = true;
        } catch (SQLException e) {
            result = false;
            log.error("删除表中数据异常："+e);
        }
        return result;
    }

    /**
     * 根据数据库名称获取表元数据
     * @param dbName 数据库名
     * @return 返回数据库表元数据
     * @throws SQLException
     */
    public List<TableMetaData> getTableMetaDataByDBName(String dbName) throws SQLException {
        Connection connection = dataSource.getDds().getConnection();
        List<TableMetaData> tablesSimple;
        try {
            tablesSimple = DatabaseAnalyzer.getTablesSimple(connection, dbName);
        } finally {
            connection.close();
        }
        return tablesSimple;
    }

    /**
     * 根据数据库名称获取表元数据
     * @param dbName 数据库名
     * @param otherUser 其他实例名
     * @return 返回数据库表元数据
     * @throws SQLException
     */
    public List<TableMetaData> getTableMetaDataByDBName(String dbName, String otherUser) throws SQLException {
        Connection connection = dataSource.getDds().getConnection();
        List<TableMetaData> tablesSimple;
        try {
            tablesSimple = DatabaseAnalyzer.getTablesSimple(connection, dbName, otherUser);
        } finally {
            connection.close();
        }
        return tablesSimple;
    }

    /**
     * 获取主键元数据集合
     * @param dbName    库名称
     * @param tableName 表名称
     * @return List<PrimaryKeyMetaData>
     * @throws Exception
     */
    public List<PrimaryKeyMetaData> getPrimaryKeyList(String dbName, String tableName) throws Exception {
        Connection connection = dataSource.getDds().getConnection();
        DatabaseMetaData metaData = connection.getMetaData();
        List<PrimaryKeyMetaData> primaryKeyMetaDatas;
        try {
            primaryKeyMetaDatas = DatabaseAnalyzer.getPrimaryKeys(metaData, dbName, tableName);
        } finally {
            connection.close();
        }
        return primaryKeyMetaDatas;
    }


    /**
     * 关闭数据源
     * @throws SQLException
     */
    public void closeDataSource() throws SQLException {
        dataSource.closeDataSource();
    }
}
